﻿using System;
using System.Collections.Generic;
using System.Linq;
using InvestmentIntelligence.Data.Repository.Comparers.EntityModelsComparers;
using InvestmentIntelligence.DbModel;
using InvestmentIntelligence.DbModel.Models;
using Security = InvestmentIntelligence.DbModel.Models.Security;

namespace InvestmentIntelligence.Data.Repository.EFImplementation
{
    using System.Data.Entity;

    public class SecurityRepository : IIPRepository<Security>, ISecurityRepository, ISecurityUpdatableRepository
    {
        #region Private Fields

        private const string RecursiveSecurities = @"
;WITH cte
AS
(
	--anchor	
    {0}	

	UNION ALL

	--recursive member
	SELECT s0.* FROM [MarketData].[SecurityView] s0	
	INNER JOIN cte s1
	ON s1.ParentId = s0.Id
)

SELECT DISTINCT * FROM cte
ORDER BY ParentId";

        #endregion

        public new int Add(Security security)
        {
            base.Add(security);
            return security.Id;
        }

        public void Save(IEnumerable<Security> securitylist, IEqualityComparer<Security> keyComparer = null, IEqualityComparer<Security> fullComparer = null)
        {
            if (keyComparer == null)
            {
                keyComparer = new DefaultSecurityComparer();
            }
            if (fullComparer == null)
            {
                fullComparer = new SecurityEqualityComparer();
            }
            ProccesSaving(securitylist.ToList(), keyComparer, fullComparer);
        }

        public Security Get(int id)
        {
            return base.Get(dc => dc
                .Include("EconomicSector")
                .Include("SecurityClass")
                .Include("Country")
                .Include("Currency")
                .SingleOrDefault(x => x.Id == id));
        }



        public new List<Security> GetList()
        {
            return GetList(dc => dc.Include("Currency")
                .Include("Country")
                .Include("EconomicSector")
                .Include("SecurityClass"));
        }



        public List<T> GetListByEconomicSectorId<T>(Func<IQueryable<Security>, IEnumerable<T>> f, int id)
        {
            return
                GetList(
                    dc =>
                       f(dc.Include("Currency")
                            .Include("Country")
                            .Include("EconomicSector")
                            .Include("SecurityClass")
                            .Where(x => x.EconomicSectorId == id)));
        }

        public List<T> GetListByCountryId<T>(Func<IQueryable<Security>, IEnumerable<T>> f, int id)
        {
            return
              GetList(
                  dc =>
                     f(dc.Include("Currency")
                          .Include("Country")
                          .Include("EconomicSector")
                          .Include("SecurityClass")
                          .Where(x => x.CountryId == id)));
        }
        /*
        public List<SecurityIdentityModel> CheckExistingAndReturnUnExistingIdentities(List<SecurityIdentityModel> list)
        {
            var returnList = new List<SecurityIdentityModel>();
            var isinTypeList = list.Where(x => x.IdentityType == IdentityType.Isin).ToList();
            var ricTypeList = list.Where(x => x.IdentityType == IdentityType.Ric).ToList();
            var sedolTypeList = list.Where(x => x.IdentityType == IdentityType.Sedol).ToList();
            var micTypeList = list.Where(x => x.IdentityType == IdentityType.Mic).ToList();
            var customTypeList = list.Where(x => x.IdentityType == IdentityType.CustomIdentificator).ToList();
            var tickerTypeList = list.Where(x => x.IdentityType == IdentityType.Ticker).ToList();

            var isinIdentities = isinTypeList.Select(x => x.IdentityString);
            var existIsinsList = GetList(c => c.Where(x => isinIdentities.Contains(x.Isin)).Select(x => x.Isin));
            returnList.AddRange(isinTypeList.Select(x => x.IdentityString).Except(existIsinsList).Where(x => !string.IsNullOrWhiteSpace(x)).Select(x => new SecurityIdentityModel(IdentityType.Isin, x)).ToList());


            var ricdentities = ricTypeList.Select(x => x.IdentityString);
            var existRicList = GetList(c => c.Where(x => ricdentities.Contains(x.Ric)).Select(x => x.Ric));
            returnList.AddRange(ricTypeList.Select(x => x.IdentityString).Except(existRicList).Where(x => !string.IsNullOrWhiteSpace(x)).Select(x => new SecurityIdentityModel(IdentityType.Ric, x)).ToList());


            var sedoldentities = sedolTypeList.Select(x => x.IdentityString);
            var existSedolList = GetList(c => c.Where(x => sedoldentities.Contains(x.Sedol)).Select(x => x.Sedol));
            returnList.AddRange(sedolTypeList.Select(x => x.IdentityString).Except(existSedolList).Where(x => !string.IsNullOrWhiteSpace(x)).Select(x => new SecurityIdentityModel(IdentityType.Sedol, x)).ToList());

            var micIentities = micTypeList.Select(x => x.IdentityString);
            var existMiclList = GetList(c => c.Where(x => micIentities.Contains(x.Mic)).Select(x => x.Mic));
            returnList.AddRange(micTypeList.Select(x => x.IdentityString).Except(existMiclList).Where(x => !string.IsNullOrWhiteSpace(x)).Select(x => new SecurityIdentityModel(IdentityType.Mic, x)).ToList());

            var customIentities = customTypeList.Select(x => x.IdentityString);
            var existCustomlList = GetList(c => c.Where(x => customIentities.Contains(x.CustomIdentificator)).Select(x => x.CustomIdentificator));
            returnList.AddRange(customTypeList.Select(x => x.IdentityString).Except(existCustomlList).Where(x => !string.IsNullOrWhiteSpace(x)).Select(x => new SecurityIdentityModel(IdentityType.CustomIdentificator, x)).ToList());

            var tickerIentities = tickerTypeList.Select(x => x.IdentityString);
            var existTickerList = GetList(c => c.Where(x => tickerIentities.Contains(x.Ticker)).Select(x => x.Ticker));
            returnList.AddRange(tickerTypeList.Select(x => x.IdentityString).Except(existTickerList).Where(x => !string.IsNullOrWhiteSpace(x)).Select(x => new SecurityIdentityModel(IdentityType.Ticker, x)).ToList());

            return returnList;
        }
        */
        


        /// <summary>
        /// Save SecurityList using EF
        /// </summary>
        /// <param name="tempSecurityList"></param>
        /// <param name="keyComparer">Compare Natural Key for given securities. Used for adding new items</param>
        /// <param name="fullComparer">Compare all Secutity fields. Used for modifying items</param>
        internal void ProccesSaving(ICollection<Security> tempSecurityList, IEqualityComparer<Security> keyComparer, IEqualityComparer<Security> fullComparer)
        {
            //TODO New Version need to test
            var securityList = GetList();
            var securitiesToAdd = tempSecurityList.Except(securityList, keyComparer).ToList();
            var securitiesToUpdate = tempSecurityList.Except(securitiesToAdd, fullComparer).ToList();

            BulkInsert(securitiesToAdd);

            foreach (var security in securitiesToUpdate)
            {
                var securs = securityList.Where(x => fullComparer.Equals(x, security)).ToList();
                if (securs.Count() != 1)
                    continue;
                var originalSecurity = securs[0];
                security.Id = originalSecurity.Id;
            }
            Update(securitiesToUpdate.Where(x => x.Id != 0));
        }

        public List<T> Get<T>(Func<IQueryable<Security>, IEnumerable<T>> f, IEnumerable<int> ids)
        {
            return GetList(dc => f(dc.Include("EconomicSector")
                                                  .Include("SecurityClass")
                                                  .Include("Country")
                                                  .Include("Currency")
                                                  .Where(c => ids.Contains(c.Id))));
        }

        private static readonly List<SecurityView> EmptySecurities = new List<SecurityView>(); 

        public List<SecurityView> GetTreeSecuritiesByText(string term, int limit, string additionalFilter = null)
        {
            using (var dc = new IIPEntities())
            {
                var fts = string.Format(@"SELECT TOP {0} * FROM [MarketData].[SecurityView]", limit);
                var items = term.Split(' ')
                            .Where(c => !string.IsNullOrWhiteSpace(c) && c.Length > 1)
                            .Select(c => string.Format(@"(CONTAINS(*, '""{0}*""'))", c));
                var filter = string.Join(" AND ", items);

                if (string.IsNullOrWhiteSpace(filter))
                {
                    return EmptySecurities;
                }

                var query = string.Format("{0} WHERE {1}", fts, filter);
                if (!string.IsNullOrEmpty(additionalFilter))
                    query += " AND " + filter;
                return dc.Database.SqlQuery<SecurityView>(string.Format(RecursiveSecurities, query)).ToList();
            }
        }

        public List<SecurityView> FindBy(string term, string field)
        {
            using (var dc = new IIPEntities())
            {
                var fts = string.Format(@"SELECT * FROM [MarketData].[SecurityView]");
                var filter = GetFilter(term, field);            
                var query = string.Format("{0} WHERE {1}", fts, filter);
                return dc.Database.SqlQuery<SecurityView>(string.Format(RecursiveSecurities, query)).ToList();
            }
        }

        private string GetFilter(string term, string field)
        {
            const string template = "({0} = '{1}' OR {0} LIKE '{1}[^A-Z]%') and contains({0}, '{2}')";
                var ftsCondition = string.Join(" AND ", term.Split(new []{" "}, StringSplitOptions.RemoveEmptyEntries));
            return string.Format(template, field, term, ftsCondition);
        }

        public List<SecurityView> GetTreeSecurities(IEnumerable<int> securityIds)
        {
            const string cmd = @"
SELECT * FROM [MarketData].[SecurityView]
WHERE Id IN ({0})";

            var query = string.Format(cmd, string.Join(",", securityIds));

            using (var db = new IIPEntities())
            {
                return db.Database.SqlQuery<SecurityView>(string.Format(RecursiveSecurities, query)).ToList();
            }
        }
    }
}
